package nbp.currency.data;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import nbp.currency.logic.entity.Currency;
import nbp.currency.logic.entity.CurrencyExchange;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class CurrencyExchangeDAOImpl implements CurrencyExchangeDAO {

	SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();

	@Override
	public CurrencyExchange addCurrencyExchange(CurrencyExchange currencyExchange, Long idCurrency) {

		Session session = sessionFactory.openSession();
		Transaction transaction = session.beginTransaction();

		Currency currency = (Currency) session.load(Currency.class, idCurrency);
		currencyExchange.setCurrency(currency);
		session.merge(currencyExchange);

		transaction.commit();
		session.close();

		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public CurrencyExchange getCurrencyExchange(Long currencyId, Date date) {
		Session session = sessionFactory.openSession();
		Transaction transaction = session.beginTransaction();

		Query createQuery = session.createQuery("from CurrencyExchange as ce where ce.currency.id=:id AND ce.date=:date").setLong("id", currencyId)
				.setDate("date", date);
		List<CurrencyExchange> list = createQuery.list();

		transaction.commit();
		session.close();

		if (list.size() == 1) {
			for (CurrencyExchange currencyExchange : list) {
				System.out.println(currencyExchange);
			}

			return list.get(0);
		}

		return null;
	}

	@Override
	public Collection<CurrencyExchange> getTop5Exchange(Long idCurrency) {

		Session session = sessionFactory.openSession();
		Transaction transaction = session.beginTransaction();

		Query query = session.createQuery("FROM CurrencyExchange ce WHERE ce.currency.id=:id ORDER BY ce.averageExchange DESC")
				.setLong("id", idCurrency).setMaxResults(5);

		List<CurrencyExchange> list = query.list();
		for (CurrencyExchange currencyExchange : list) {
			System.out.println(currencyExchange);
		}
		transaction.commit();
		session.close();
		return list;
	}

	@Override
	public void updateCurrencyExchange(CurrencyExchange currencyExchange) {

		CurrencyExchange currencyExchange2 = new CurrencyExchange();
		currencyExchange2.setId(88l);
		currencyExchange2.setAverageExchange(BigDecimal.ONE);

		Session session = sessionFactory.openSession();
		session.beginTransaction();
		session.update(currencyExchange2);
		session.getTransaction().commit();
		session.close();

	}

	@Override
	public void deleteCurrencyExchange(CurrencyExchange currencyExchange) {
		Session session = sessionFactory.openSession();
		Transaction trans = session.beginTransaction();
		session.delete(currencyExchange);
		trans.commit();
		session.close();

	}

	@Override
	public void deleteCurrencyExchange(Long idCurrency, Date date) {
		Session session = sessionFactory.openSession();
		Transaction trans = session.beginTransaction();
		Query query = session.createQuery("DELETE FROM CurrencyExchange ce WHERE ce.currency.id=:id AND ce.date=:date").setLong("id", idCurrency)
				.setDate("date", date);
		query.executeUpdate();
		trans.commit();
		session.close();

	}

	@Override
	public Long getBiggestDifferenceExchange(Date dateFrom, Date dateTo) {
		Session session = sessionFactory.openSession();
		Transaction transaction = session.beginTransaction();

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-ddd");
		String dateFromString = dateFormat.format(dateFrom);

		String dateToString = dateFormat.format(dateTo);

		Query createQuery;
		createQuery = session
				.createSQLQuery("select p.exchange as poczatek, k.exchange as koniec, k.id_currency, (k.exchange-p.exchange)/k.exchange*100 as procent from "
						+ "(select exchange, id_currency from currency_exchange where public_date = '"
						+ dateFromString
						+ "') as p LEFT "
						+ "JOIN ( select exchange, id_currency from currency_exchange where public_date = '"
						+ dateToString
						+ "') as k ON p.id_currency=k.id_currency order by 4 desc limit 1;");

		List<Object[]> list = createQuery.list();
		if (list.size() == 1) {

			return ((BigDecimal) list.get(0)[3]).longValue();
		}

		return 0l;
	}

	@Override
	public BigDecimal getMaxExchange(Date dateFrom, Date dateTo, Long idCurrency) {
		Session session = sessionFactory.openSession();
		Transaction beginTransaction = session.beginTransaction();

		Query query = session
				.createQuery(
						"SELECT MAX(ce.averageExchange) FROM CurrencyExchange AS ce WHERE ce.currency.id=:id AND date BETWEEN :dateFrom AND :dateTo GROUP BY ce.currency.id")
				.setLong("id", idCurrency).setDate("dateTo", dateTo).setDate("dateFrom", dateFrom);
		List<BigDecimal> result = query.list();

		beginTransaction.commit();
		session.close();

		if (result.size() == 1) {
			return result.get(0);
		}

		return null;
	}

	@Override
	public BigDecimal getMinExchange(Date dateFrom, Date dateTo, Long idCurrency) {
		Session session = sessionFactory.openSession();
		Transaction beginTransaction = session.beginTransaction();

		Query query = session
				.createQuery(
						"SELECT MIN(ce.averageExchange) FROM CurrencyExchange AS ce WHERE ce.currency.id=:id AND date BETWEEN :dateFrom AND :dateTo GROUP BY ce.currency.id")
				.setLong("id", idCurrency).setDate("dateTo", dateTo).setDate("dateFrom", dateFrom);
		BigDecimal result = (BigDecimal) query.uniqueResult();

		return result;
	}

	public void test() {

		Session openSession = sessionFactory.openSession();
		openSession.beginTransaction();

		Currency currency = new Currency();
		currency.setId(1l);
//		currency.setCurrency("LIR_3");sz
		

		CurrencyExchange currencyExchange = new CurrencyExchange();
		currencyExchange.setDate(new Date());
		currencyExchange.setCurrency(currency);
		currencyExchange.setConverter(2);
		currencyExchange.setAverageExchange(new BigDecimal("1111"));

		openSession.persist(currencyExchange);

		openSession.getTransaction().commit();
		openSession.close();

	}

	
	//2.4  6
	//2.4 USD;PLN
	@SuppressWarnings("unchecked")
	public void query(Date dateFrom, Date dateTo) {
		Session openSession = sessionFactory.openSession();
		openSession.beginTransaction();

		List<Object[]> list = openSession
				.createQuery(
						"select ce.averageExchange, count(ce.averageExchange) from CurrencyExchange ce WHERE ce.public_date BETWEEN =:dateFrom AND =:dateTo GROUP BY ce.averageExchange HAVING(count(ce.averageExchange)>2)")
				.list();

		List<BigDecimal> exchanges = new ArrayList<BigDecimal>();

		for (Object[] objects : list) {
			exchanges.add((BigDecimal) objects[0]);
		}

		List<Object[]> list2 = openSession
				.createQuery("select ce.averageExchange, ce.currency.currency from CurrencyExchange ce where ce.averageExchange IN(:ids)")
				.setParameterList("ids", exchanges).list();

		Map<BigDecimal, List<String>> map = new HashMap<BigDecimal, List<String>>();

		for (Object[] objects : list2) {
			BigDecimal exchange = (BigDecimal) objects[0];
			if (!map.containsKey(exchange)) {
				map.put(exchange, new ArrayList<String>());
			}
			map.get(exchange).add((String) objects[1]);
		}

	}

	public static void main(String[] args) {
		CurrencyExchangeDAOImpl daoImpl = new CurrencyExchangeDAOImpl();
		Calendar cal = new GregorianCalendar();
		cal.set(2014, 10, 3);
		Calendar cal2 = new GregorianCalendar();
		cal2.set(2014, 11, 1);
		daoImpl.test();
	}

}
